<%@ page pageEncoding="UTF-8"
         import="org.apache.poi.hssf.usermodel.*,
				 org.apache.poi.ss.util.CellRangeAddress,
         org.apache.poi.ss.usermodel.*,
         java.sql.*,
         java.io.*" %><%
		response.reset();
    response.setContentType("application/vnd.ms-excel");
    response.setHeader( "Content-Disposition" ,"attachment; filename=\"excel.xls\"" );
    String resultSetType = request.getParameter("resultSetType");
    String columnWidth = request.getParameter("columnWidth");
    String columnSum = request.getParameter("columnSum");
    String columnName = request.getParameter("columnName");

    String sql = request.getParameter("sql");
    String bw = request.getParameter("bw");
    String mainHeader = request.getParameter("mainHeader");
    String subHeader = request.getParameter("subHeader");
	  if (bw.equals("ie")) {
    		mainHeader = new String(mainHeader.getBytes("ISO8859-1"), "GB2312");
    		subHeader = new String(subHeader.getBytes("ISO8859-1"), "GB2312");
	      columnName = new String(columnName.getBytes("ISO8859-1"), "GB2312");
	      sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
	  } else {
    		mainHeader = new String(mainHeader.getBytes("ISO8859-1"), "UTF-8");
    		subHeader = new String(subHeader.getBytes("ISO8859-1"), "UTF-8");
	      columnName = new String(columnName.getBytes("ISO8859-1"), "UTF-8");
	      sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
	  }
%><%
class toExcel {
    //poi obj
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFCellStyle style;
    private HSSFCellStyle columnStyle1[];
    //Excel cfg
    private short columnHeadHeight = 1;
    private int columnWidth [] = {};
    private int countColRow = 0;
    private String columnName [] = {};
    private int columnSum [] = {};
    private int firstRow = 1;
    private int firstCol = 1;
    private String columnMergingRegex = "|";
    //data source
    private ResultSet resultSet;
    private short resultSetType [];

    public toExcel(ResultSet _rs ,int _resultSetType[] ){
        resultSet = _rs;
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("new sheet");
        style = wb.createCellStyle();
        style.setBorderBottom((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setBorderTop((short)1);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    }

    public toExcel(ResultSet _rs ,short _resultSetType[] ,String _columnName[] ,int _columnWidth[]){
        resultSet = _rs;
        resultSetType = _resultSetType;
        columnName = _columnName;
        columnWidth = _columnWidth;
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("new sheet");
        style = wb.createCellStyle();
        style.setBorderBottom((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setBorderTop((short)1);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        columnStyle1 = new HSSFCellStyle[resultSetType.length];
        for(int i = 0 ;i < resultSetType.length ;i++){
            columnStyle1[i]=wb.createCellStyle();
            columnStyle1[i].setBorderBottom((short)1);
            columnStyle1[i].setBorderLeft((short)1);
            columnStyle1[i].setBorderRight((short)1);
            columnStyle1[i].setBorderTop((short)1);
            columnStyle1[i].setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        }
    }

    public toExcel(ResultSet _rs ,String _resultSetType ,String _columnName ,String _columnWidth ,String _regex){
        resultSet = _rs;
        resultSetType = StringToShortArray( _resultSetType ,_regex);
        columnName = _columnName.split("["+_regex+"]");
        columnWidth = StringToIntArray( _columnWidth,_regex);
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("new sheet");
        style = wb.createCellStyle();
        style.setBorderBottom((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setBorderTop((short)1);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        columnStyle1 = new HSSFCellStyle[resultSetType.length];
        for(int i = 0 ;i < resultSetType.length ;i++){
            columnStyle1[i]=wb.createCellStyle();
            columnStyle1[i].setBorderBottom((short)1);
            columnStyle1[i].setBorderLeft((short)1);
            columnStyle1[i].setBorderRight((short)1);
            columnStyle1[i].setBorderTop((short)1);
            columnStyle1[i].setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        }
    }

    private int[] StringToIntArray(String _intArrayStr ,String _regex){
        String strArray[] = _intArrayStr.split("["+_regex+"]");
        int length = strArray.length;
        int intArray[] = new int[length];
        for(int i = 0;i<length;++i){
            try{
                intArray[i] = Integer.parseInt(strArray[i]);
            }catch(NumberFormatException ex){
                intArray[i] = 0;
            }
        }
        return intArray;
    }

    private short[] StringToShortArray(String _intArrayStr ,String _regex){
        String strArray[] = _intArrayStr.split("["+_regex+"]");
        int length = strArray.length;
        short shortArray[] = new short[length];
        for(int i = 0;i<length;++i){
            try{
                shortArray[i] = (short)Integer.parseInt(strArray[i]);
            }catch(NumberFormatException ex){
                shortArray[i] = 0;
            }
        }
        return shortArray;
    }

    public void setResultSetType(short _resultSetType[]){
        resultSetType = _resultSetType;
    }

    public void setColumnSum(int _columnSum[]){
        columnSum = _columnSum;
    }

    public void setColumnSum(String _columnSum ,String _regex){
        setColumnSum(StringToIntArray(_columnSum,_regex));
    }

    public void setColumnWidth(int _columnWidth[]){
        columnWidth = _columnWidth;
    }

    public void setColumnWidth(String _columnWidth ,String _regex){
        setColumnSum(StringToIntArray(_columnWidth,_regex));
    }

    public void setSheetFreezePane(int colSplit ,int rowSplit ){
        sheet.createFreezePane( colSplit+firstCol,rowSplit+firstRow );
    }

    private int initColumnNameArray(String _regex){
        int result=0;
        int countColumn = columnName.length;
        if(countColumn <= 0){
            try{
                ResultSetMetaData metaData = resultSet.getMetaData();
                countColumn = metaData.getColumnCount();
                columnName = new String [countColumn];
                for(int i=0 ;i<countColumn ;++i){
                    columnName[i] = metaData.getColumnLabel(i+1);
                }
            }catch(Exception ex){
                System.err.println(ex.getMessage());
            }
        }
        for(int i=0 ; i<countColumn ; ++i){
            if(columnName[i].split("["+_regex+"]").length>result){
                result = columnName[i].split("["+_regex+"]").length;
            }
        }
        return result;
    }

    private int createExcelColumn(short _columnHeight ,String _regex){
        int countColumn = -1;
        countColRow = initColumnNameArray(_regex);
        try{
            countColumn = columnName.length;
            for(int ii = 0 ; ii<countColRow ; ++ii){
                HSSFRow columnRow = addRow( firstRow + ii );
                for(int i = 0 ; i<countColumn ; ++i){
                    int len = columnName[i].split("[|]").length;
                    String colName = ii>(len-1) ? columnName[i].split("[|]")[len-1] : columnName[i].split("[|]")[ii];
                    addColumnCell(columnRow,i+1,colName,style);
                    if(columnWidth.length > 0 && i<columnWidth.length){
                        sheet.setColumnWidth(i+1,columnWidth[i]*32);
                    }
                }
                columnRow.setHeight( (short)(columnRow.getHeight() * _columnHeight) );
            }
            //sheet.addMergedRegion(new CellRangeAddress( 1,1,1,2 ));
            columnMerging(firstRow,firstCol,countColRow,columnName,_regex);
        }catch(Exception ex){
            System.err.println(ex.getMessage());
            ex.printStackTrace();
        }finally{
            try{
            }catch( Exception ex ){
                System.err.println(ex.getMessage());
                ex.printStackTrace();
            }
        }
        return countColumn;
    }

    private void columnMerging(int firstRow ,int firstCol ,int _countColRow ,String _columnName[] ,int fIndex ,int arrayLen ,String _regex){
        String tmp="";
        for(int i = fIndex ; i<arrayLen ; ++i){
            tmp=tmp+"["+_columnName[i]+"]";
        }
        System.err.println("String:{"+tmp+"}");
        String firstColName = null;
        String _Tmp [] = _columnName;
        if(_countColRow<=1)return;
        for(int i = fIndex ; i<arrayLen ;++i){
            int len = _columnName[i].split("["+_regex+"]").length;
            if(len == 1){
                System.err.println("index("+String.valueOf(i)+"):["+_columnName[i]+"]");
                System.err.println("(1)FirstRow:["+String.valueOf(firstRow)+"];LastRow:["+String.valueOf(firstRow+_countColRow-1)+"];FirstCol:["+String.valueOf(firstCol+i)+"];LastCol:["+String.valueOf(firstCol+i)+"]");
                sheet.addMergedRegion(new CellRangeAddress( firstRow,firstRow+_countColRow-1,firstCol+i,firstCol+i ));
            }else{

                int ffirstCol=firstCol+i;
                int tmpStart = i;
                int tmpEnd = i;
                for(int j = 0;i<arrayLen ;++i,++j){
                    if(j==0){
                        firstColName=_columnName[i].split("[|]")[0];
                    }
                    if(columnName[i].split("[|]")[0].equals(firstColName)){
                        _Tmp[i] = _columnName[i].substring(_columnName[i].indexOf(_regex)+1);
                        //System.err.println("["+columnName[i].substring(columnName[i].indexOf("|")+1)+"]");
                        continue;
                    }else {
                        System.err.println("(2)FirstRow:["+String.valueOf(firstRow)+"];LastRow:["+String.valueOf(firstRow)+"];FirstCol:["+String.valueOf(ffirstCol)+"];LastCol:["+String.valueOf(ffirstCol+j-1)+"]");
                        sheet.addMergedRegion(new CellRangeAddress( firstRow,firstRow,ffirstCol,ffirstCol+j-1 ));
                        //System.err.println("["+String.valueOf(i)+"]");
                        //System.err.println("["+String.valueOf(_Tmp.length-i-1)+"]");
                        columnMerging( firstRow+1 , firstCol , _countColRow-1 , _Tmp ,tmpStart ,tmpStart+j ,_regex);
                        i--;
                        break;
                    }
                }
            }
        }
    }

    public void insertFullHeader(String _headerStr ,float _headerHeight ,short _fontSize ,int _alignment){
        HSSFRow row = addRow(firstRow++);
        row.setHeight( (short)(row.getHeight() * _headerHeight) );
        HSSFCell cell = row.createCell(firstCol);
        cell.setCellValue(_headerStr);
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints(_fontSize);
        HSSFCellStyle _style = wb.createCellStyle();
        _style.setFont(font);
        if(_alignment==0){
            _style.setAlignment(CellStyle.ALIGN_LEFT);
        }
        if(_alignment==1){
            _style.setAlignment(CellStyle.ALIGN_CENTER);
        }
        if(_alignment==2){
            _style.setAlignment(CellStyle.ALIGN_RIGHT);
        }
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(_style);
        sheet.addMergedRegion(new CellRangeAddress( firstRow-1,firstRow-1,firstCol,firstCol+columnWidth.length-1 ));
    }

    private void columnMerging(int firstRow ,int firstCol ,int _countColRow ,String _columnName[] ,String _regex ){
        columnMerging( firstRow , firstCol , _countColRow , _columnName ,0 ,_columnName.length ,_regex);
    }

    private int createExcelColumn(String _regex){
        return createExcelColumn(columnHeadHeight,_regex);
    }

    public void setColumnMergingRegex(String _regex){
        columnMergingRegex = _regex;
    }

    public void resultSetToExcel(){
        int countColumn = createExcelColumn(columnMergingRegex);
        int rowIndex = firstRow + countColRow;
        int firstRecIndex = rowIndex;
        try{
            while(resultSet!=null && resultSet.next()){
                HSSFRow row = addRow(rowIndex++);
                row.setHeight((short)(row.getHeight()));
                for (int i = 1; (i <= countColumn && (i-1)<resultSetType.length); i++) {
                    switch(resultSetType[i-1]){
                        case 0 : String valueStr;
                                 try{
                                     valueStr = resultSet.getString(i);
                                 }catch(Exception ex){
                                     valueStr = "";
                                 }
                                 addCell(row ,i ,valueStr ,columnStyle1[i-1]);
                                 break;
                        case 1 :
                        case -2:
                        case 2 : 
                        case 4 :
                        case 6 : double valueDouble;
                                 try{
                                     valueDouble = resultSet.getDouble(i);
                                 }catch(Exception ex){
                                     valueDouble = 0;
                                 }
                                 addCell(row ,i ,valueDouble ,columnStyle1[i-1] ,resultSetType[i-1]);
                                 break;                        
                        case 11 :java.util.Date valueDate = resultSet.getDate(i);
                                 addCell(row ,i ,valueDate ,columnStyle1[i-1]);
                                 break;
                        case 12 :boolean valueBool;
                                 try{
                                     valueBool = resultSet.getBoolean(i);
                                 }catch(Exception ex){
                                     valueBool = false;
                                 }
                                 addCell(row ,i ,valueBool ,columnStyle1[i-1]);
                                 break;
                        default: Object value;
                                 try{
                                     value = resultSet.getObject(i);
                                     addCell(row ,i ,value.toString() ,columnStyle1[i-1]);
                                 }catch(Exception ex){
                                     addCell(row ,i ,"" ,columnStyle1[i-1]);
                                 }                                 
                    }
                }
            }

            if(columnSum.length>0 && rowIndex > (firstRecIndex) ){
                HSSFRow row = addRow(rowIndex++);
                row.setHeight( (short)(row.getHeight()) );	//20111026
                for (int i = 1; (i <= countColumn && (i-1)<columnSum.length); i++) {
                    switch(columnSum[i-1]){
                        case 1 : addFormulaCell( row ,i ,"SUM("+ getCellName(firstRecIndex ,i)+":"+ getCellName(rowIndex-firstRecIndex ,i)+")" ,columnStyle1[i-1] );
                                 break;
                        case 2 : addFormulaCell( row ,i ,"AVERAGE("+ getCellName(firstRecIndex ,i)+":"+ getCellName(rowIndex-firstRecIndex ,i)+")" ,columnStyle1[i-1] );
                                 break;
                        default: addFormulaCell( row ,i ,"" ,columnStyle1[i-1] );
                    }
                }

            }
        }catch( SQLException ex ){
            System.err.println(ex.getMessage());
            ex.printStackTrace();
        }
    }

    public void outputExcel(OutputStream stream){
        try{
            wb.write(stream);
            stream.flush();
            stream.close();
        }catch(IOException ex){
            System.err.println(ex.getMessage());
            ex.printStackTrace();
        }
    }

    private HSSFRow addRow( int rowNum ){
        return sheet.createRow( rowNum );
    }

    private String getCellName(int row ,int column){
        char letter[] = {'A' ,'B' ,'C' ,'D' ,'E' ,'F' ,'G' ,'H' ,'I' ,'J' ,'K' ,'L' ,'M' ,'N' ,'O' ,'P' ,'Q' ,'R' ,'S' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Y' ,'Z'};
        String result = "";
        for( ; ; ){
            result = letter[(column%26)] + result;
            if( ((column/26)-1) < 0){
                break;
            }else{
                column = (column/26)-1;
            }
        }
        result = result + Integer.toString(row+1);
        return result;
    }

    private void addCell( HSSFRow row ,int cellColumn ,String value ,int fontSize){
        HSSFCell cell = row.createCell(cellColumn);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }


    private void addCell( HSSFRow row ,int cellColumn ,double value ,HSSFCellStyle style , short formatType){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        HSSFDataFormat format = wb.createDataFormat();
        cell.setCellValue(value);
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        _style.setAlignment(CellStyle.ALIGN_RIGHT);
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        switch(formatType){
            case -2 : _style.setDataFormat(format.getFormat("0%"));
                     break;
            case 2 : _style.setDataFormat(format.getFormat("0.00"));
                     break;
            case 4 : _style.setDataFormat(format.getFormat("0.0000"));
                     break;
            case 6 : _style.setDataFormat(format.getFormat("0.000000"));
                     break;
        }
        cell.setCellStyle(_style);
    }

    private void addCell( HSSFRow row ,int cellColumn ,double value ,HSSFCellStyle style , String formatType){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        HSSFDataFormat format = wb.createDataFormat();
        cell.setCellValue(value);
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        _style.setAlignment(CellStyle.ALIGN_RIGHT);
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        if(!formatType.isEmpty()){
            _style.setDataFormat(format.getFormat(formatType));
        }
        cell.setCellStyle(_style);
    }

    private void addCell( HSSFRow row ,int cellColumn ,boolean value ,HSSFCellStyle style ){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        cell.setCellValue(value);
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        _style.setAlignment(CellStyle.ALIGN_CENTER);
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(_style);
    }
    
    private void addCell( HSSFRow row ,int cellColumn ,java.util.Date value ,HSSFCellStyle style ){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        cell.setCellValue(value);
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        _style.setAlignment(CellStyle.ALIGN_CENTER);
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(_style);
    }

    private void addCell( HSSFRow row ,int cellColumn ,String value ,HSSFCellStyle style ){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        cell.setCellValue(value);
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        _style.setAlignment(CellStyle.ALIGN_LEFT);
        _style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(_style);
    }

    private void addColumnCell( HSSFRow row ,int cellColumn ,String value ,HSSFCellStyle style ){
        HSSFCell cell = row.createCell(cellColumn);
        cell.setCellValue(value);
        style = wb.createCellStyle();
        style.setBorderBottom((short)1);
        style.setBorderLeft((short)1);
        style.setBorderRight((short)1);
        style.setBorderTop((short)1);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font font = wb.createFont();
        font.setFontHeightInPoints((short)12);
        font.setFontName("黑体");
        style.setFont(font);
        cell.setCellStyle(style);
    }

    private void addFormulaCell( HSSFRow row ,int cellColumn ,String formulaString ,HSSFCellStyle style ){
        HSSFCell cell = row.createCell(cellColumn);
        HSSFCellStyle _style;
        if(!formulaString.isEmpty()){
            cell.setCellFormula(formulaString);
        }else{
            cell.setCellValue("");
        }
        if(style == null){
            _style = wb.createCellStyle();
            _style.setBorderBottom((short)1);
            _style.setBorderLeft((short)1);
            _style.setBorderRight((short)1);
            _style.setBorderTop((short)1);
        }else{
            _style = style;
        }
        cell.setCellStyle(_style);
    }
}
%><%
//http://localhost/demo/demoExcel.jsp?resultSetType=0:0:0:4:2&columnWidth=150:300:90:100:100&columnSum=0:0:0:1:2&columnName=商品编号:商品名称:计量单位:现市场单价1:现市场单价2
//    int resultSetType[] = {0,0,0,4,2};//返回值类型 0:字符型,1:数值型(int,double;小数位数自适应),2:两位小数,4:四位小数
//    int columnWidth[] ={150,300,90,100,100};//宽度
//    int columnSum[] = {0,0,0,1,2};//设置合计类型 0:不合计,1:SUM(),2:AVERAGE()
//    String columnName[]={"商品编号" ,"商品名称" ,"计量单位" ,"现市场单价1" ,"现市场单价2"};
%><%
Connection conn = null;
Statement statement = null;
try{
	Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");// SqlDriver
	conn = DriverManager.getConnection(
			"jdbc:microsoft:sqlserver://SCNSHASQL1:49301;DatabaseName=DB_SCNSHAEQMS",
			"scneqms1",
			"Asdf1234");
		
/*这一段程序是用在当浏览器传中文参数作为sql字段查询条件的时候要做字符转换;这里是要分浏览器的IE和其他浏览器有区别,所以要用if盘断一下
	bw这个值也是作为jsp参数传入的,你可以用js语句判断客户端浏览器的类型;js语句: var bw = navigator.appName == 'Microsoft Internet Explorer' ? 'ie' : 'ns';
  if (bw.equals("ie")) {
      sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
  } else {
      sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
  }
*/
	System.err.println(sql);  //调试

	statement = conn.createStatement();
	toExcel toXls = new toExcel(statement.executeQuery(sql),resultSetType,columnName,columnWidth,",");
	toXls.setColumnSum(columnSum,",");//设置合计或均值;要在resultSetToExcel()调用之前调用
	toXls.insertFullHeader(mainHeader,3,(short)18,1);//参数:(头文字,高度,字体大小,对齐方式(0:左;1中;2右))
	for(int i = 0 ; i<subHeader.split(",").length ; ++i){
		toXls.insertFullHeader(subHeader.split(",")[i],(float)1.5,(short)12,2);
	}
	//toXls.setSheetFreezePane(0,3);//列行冻结
	toXls.resultSetToExcel();
	toXls.outputExcel(response.getOutputStream());
}catch ( Exception ex ){
	ex.printStackTrace();
}finally {
	try {
        statement.close();
	} catch (Exception ex) {
		ex.printStackTrace();
	}
	try {
		conn.close();
	} catch (Exception ex) {
		ex.printStackTrace();
	}
}
%>